﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using DataTable = System.Data.DataTable;

namespace Workflows.Components.Reports
{
    public class ExcelTableWithGroupsUtil
    {
        public static void FillTableWithGroup(
            Worksheet sheet, string rangeFrom,
            List<string> colHeaders,
            Dictionary<string, string> detailTableField_ColHeader_Mappings,
            Dictionary<string, string> masterTableField_ColHeader_Mappings,
            string parentKeyFieldName, string childKeyFieldName, 
            DataTable masterTable, DataTable detailTable,
            bool showMasterContentBelowDetailContent)
        {
            sheet.Outline.ShowLevels(1, Type.Missing);
            sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;
            if (showMasterContentBelowDetailContent)
            {
                sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryBelow;
            }
            sheet.Outline.AutomaticStyles = false;

            int startingColIndex = WorksheetHelper.GetColumnIndex(rangeFrom);
            int startingRowIndex = WorksheetHelper.GetRowIndex(rangeFrom);
            // column headers
            Dictionary<string, int> colHeader_ColIndex_Mappings = new Dictionary<string, int>();
            for (int i = 0; i < colHeaders.Count; i++)
            {
                Range rng = WorksheetHelper.GetCell(sheet, startingColIndex + i, startingRowIndex);
                rng.Value2 = colHeaders[i];
                colHeader_ColIndex_Mappings.Add(colHeaders[i], startingColIndex + i);
            }
            
            // master table 
            int rowIndex = startingRowIndex;
            for (int i = 0; i < masterTable.Rows.Count; i++)
            {
                rowIndex++;
                int pkValue = (int)masterTable.Rows[i][parentKeyFieldName];
                for (int k = 0; k < masterTable.Columns.Count; k++)
                {
                    string masterColName = masterTable.Columns[k].ColumnName;
                    string masterColHeader = masterTableField_ColHeader_Mappings[masterColName];
                    int masterColIndex = colHeader_ColIndex_Mappings[masterColHeader];
                    Range rng = WorksheetHelper.GetCell(sheet, masterColIndex, rowIndex);
                    rng.Value2 = masterTable.Rows[i][k];
                }

                // detail rows
                DataRow[] drDetails = detailTable.Select(string.Format("{0}={1}", childKeyFieldName, pkValue));
                if (drDetails != null && drDetails.Length > 0)
                {
                    int groupFromIdx = rowIndex + 1;
                    int groupToIdx = 0;
                    for (int m = 0; m < drDetails.Length; m++)
                    {
                        rowIndex++;
                        for (int n = 0; n < detailTable.Columns.Count; n++)
                        {
                            string detailColName = detailTable.Columns[n].ColumnName;
                            string detailColHeader = detailTableField_ColHeader_Mappings[detailColName];
                            int detailColIndex = colHeader_ColIndex_Mappings[detailColHeader];
                            Range rng = WorksheetHelper.GetCell(sheet, detailColIndex, rowIndex);
                            rng.Value2 = detailTable.Rows[m][n];
                        }
                    }
                    groupToIdx = rowIndex;
                    Range groupRng = (Range)sheet.Rows[string.Format("{0}:{1}", groupFromIdx, groupToIdx), Type.Missing];
                    groupRng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
        }
    }
}
